﻿using DBUtil.Generators;
using DBUtil.MetaData;
using DBUtil.PostgreSql.MetaData;
using DotNetCommon.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Index = DBUtil.MetaData.Index;

namespace DBUtil.Provider.PostgreSql
{
    public class PostgreSqlManage : DBManage
    {
        private const string SCHEMA_NOTIN = "'information_schema','pg_catalog','pg_toast_temp_1','pg_temp_1','pg_toast'";

        /// <summary>
        /// 根据DBAccess创建DBManage
        /// </summary>
        /// <param name="db"></param>
        public PostgreSqlManage(DBAccess db) : base(db) { }

        #region 生成insert语句 GeneInsertSql
        #endregion

        /// <summary>
        /// 是否给表填充详细信息,默认不填充,即只有表名称/表说明(注:postgresql中没有记录表创建和修改时间)
        /// </summary>
        /// <param name="fillTableInfo"></param>
        /// <returns></returns>
        public override List<Table> ShowTables(bool fillTableInfo = false)
        {
            if (!fillTableInfo)
            {
                string sql = $@"
select *,(select description from pg_description where objoid in(select oid from pg_class where relname=t.table_name and relkind='r')) as description 
from information_schema.tables t
where 
	table_schema not in({SCHEMA_NOTIN})
	and table_type ='BASE TABLE'";
                DataTable dt = db.SelectDataTable(sql);
                List<Table> tables = new List<Table>();
                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    var tbl = new PostgreSqlTable()
                    {
                        SchemaName = dt.Rows[i]["table_schema"].ToString(),
                        Name = dt.Rows[i]["table_name"].ToString(),
                        Desc = dt.Rows[i]["description"].ToString(),
                        //TODO postgresql不能直接查询表创建时间
                        CreateTime = DateTime.MinValue
                    };
                    tables.Add(tbl);
                }
                return tables;
            }
            else
            {
                string sql = $@"select * from INFORMATION_SCHEMA.TABLES where table_schema not in({SCHEMA_NOTIN})
	and table_type ='BASE TABLE'";
                using (db.OpenLongConnectOnce())
                {
                    DataTable dt = db.SelectDataTable(sql);
                    List<Table> tables = new List<Table>();
                    for (int i = 0, len = dt.Rows.Count; i < len; i++)
                    {
                        string name = dt.Rows[i]["TABLE_NAME"].ToString();
                        var tbl = FillTableInfo(name);
                        tables.Add(tbl);
                    }
                    return tables;
                };
            }
        }

        /// <summary>
        /// 返回指定表的详细元数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName">模式名称</param>
        public override Table ShowTableDetail(string tableName, string schemaName = null)
        {
            using (db.OpenLongConnectOnce())
            {
                var table = new PostgreSqlTable() { Name = tableName };
                FillTableInfo(table);
                FillColumns(table);
                FillTriggers(table);
                FillConstraints(table);
                FillIndexes(table);
                return table;
            };
        }

        /// <summary>
        /// 根据表名填充表说明/架构
        /// </summary>
        /// <param name="table"></param>
        private void FillTableInfo(Table table)
        {
            if (table == null) return;
            string sql = $@"
select *,(select description from pg_description where objoid in(select oid from pg_class where relname=t.table_name and relkind='r')) as description 
from information_schema.tables t
where 
	table_schema not in({SCHEMA_NOTIN})
	and table_type ='BASE TABLE' and t.table_name='{table.Name}'";
            DataTable dt = db.SelectDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                table.Name = dt.Rows[0]["table_name"].ToString();
                table.Desc = dt.Rows[0]["description"].ToString();
                table.SchemaName = dt.Rows[0]["table_schema"].ToString();
            }
        }

        /// <summary>
        /// 根据表名填充列信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillColumns(Table table)
        {
            if (table == null) return;
            string sql = $@"
select ordinal_position as Colorder,column_name as ColumnName,data_type as TypeName,
coalesce(character_maximum_length,numeric_precision,-1) as Length,numeric_scale as Scale,
case is_nullable when 'NO' then 0 else 1 end as CanNull,column_default as DefaultVal,
case  when position('nextval' in column_default)>0 then 1 else 0 end as IsIdentity, 
case when b.pk_name is null then 0 else 1 end as IsPK,c.DeText
from information_schema.columns 
left join (
    select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint  
    inner join pg_class on pg_constraint.conrelid = pg_class.oid 
    inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and  pg_attr.attnum = pg_constraint.conkey[1] 
    inner join pg_type on pg_type.oid = pg_attr.atttypid
    where pg_class.relname = '{table.Name}' and pg_constraint.contype='p' 
) b on b.colname = information_schema.columns.column_name
left join (
    select attname,description as DeText from pg_class
    left join pg_attribute pg_attr on pg_attr.attrelid= pg_class.oid
    left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid=pg_attr.attnum
    where pg_attr.attnum>0 and pg_attr.attrelid=pg_class.oid and pg_class.relname='{table.Name}'
)c on c.attname = information_schema.columns.column_name
where table_schema='public' and table_name='{table.Name}' order by ordinal_position asc
";
            DataTable dt = db.SelectDataTable(sql);
            List<Column> columns = new List<Column>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    var column = new PostgreSqlColumn();
                    column.Name = dt.Rows[i]["columnname"].ToString();
                    column.TableName = table.Name;
                    //列类型
                    column.TypeString = dt.Rows[i]["typename"].ToString();
                    column.Length = int.Parse(dt.Rows[i]["length"].ToString());
                    //列说明
                    column.Desc = dt.Rows[i]["detext"].ToString();
                    //是否可空
                    column.IsNullAble = dt.Rows[i]["cannull"].ToString() == "0" ? false : true;
                    //自增
                    column.IsIdentity = dt.Rows[i]["isidentity"].ToString() == "0" ? false : true;
                    if (column.IsIdentity)
                    {
                        column.IdentityStart = 1;
                        column.IdentityIncre = 1;
                    }
                    //默认值
                    var de = dt.Rows[i]["defaultval"].ToString();
                    column.HasDefault = string.IsNullOrEmpty(de) ? false : true;
                    if (column.HasDefault) column.Default = de;
                    columns.Add(column);
                    //唯一:通过唯一约束修正
                    //主键
                    var ispk = dt.Rows[i]["ispk"].ToString();
                    if (ispk == "1")
                    {
                        table.PrimaryKeyColumns.Add(column);
                        column.IsUnique = true;
                    }
                }
            }
            table.Columns = columns;
            if (table.PrimaryKeyColumns.Count > 0)
            {
                table.PrimaryKey = string.Join(",", table.PrimaryKeyColumns.Select(col => col.Name).ToList());
            };
        }

        /// <summary>
        /// 根据表名填充触发器信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillTriggers(Table table)
        {
            table.Triggers = ShowTableTriggers(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 返回所有的视图(仅用户创建的视图)
        /// </summary>
        /// <returns></returns>
        public override List<View> ShowViews()
        {
            List<View> views = new List<View>();
            var sql = $"select * from pg_views where schemaname not in ({SCHEMA_NOTIN})";
            var dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var name = dt.Rows[i]["viewname"].ToString();
                var createSql = dt.Rows[i]["definition"].ToString();
                var schema = dt.Rows[i]["schemaname"].ToString();
                var view = new PostgreSqlView()
                {
                    Name = name,
                    CreateSql = createSql,
                    SchemaName = schema
                };
                views.Add(view);
            }
            return views;
        }

        public override List<Procedure> ShowProcedures()
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 返回触发器集合
        /// </summary>
        /// <param name="tableName">所属的表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<Trigger> ShowTableTriggers(string tableName, string schemaName)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return new List<Trigger>();
            List<Trigger> triggers = new List<Trigger>();
            string sql = $@"
SELECT *  
  FROM information_schema.triggers  
 WHERE event_object_table='{tableName}' and trigger_schema NOT IN  
       ('information_schema','pg_catalog','pg_toast_temp_1','pg_temp_1','pg_toast');   
";
            DataTable dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                string name = dt.Rows[i]["trigger_name"].ToString();
                string schema = dt.Rows[i]["trigger_schema"].ToString();
                var event_manipulation = dt.Rows[i]["event_manipulation"].ToString();
                bool isUpdate = event_manipulation.Contains("UPDATE");
                bool isDelete = event_manipulation.Contains("DELETE");
                bool isInsert = event_manipulation.Contains("INSERT");
                bool isAfter = dt.Rows[i]["action_timing"].ToString() == "AFTER";
                var statement = dt.Rows[i]["action_statement"].ToString();
                Trigger tri = new PostgreSqlTrigger()
                {
                    Name = name,
                    CreateSql = statement,
                    TableName = tableName,
                    IsInsert = isInsert,
                    IsUpdate = isUpdate,
                    IsDelete = isDelete,
                    IsAfter = isAfter,
                    IsInsteadof = false,
                    ActionStatement = statement
                };
                triggers.Add(tri);
            }
            return triggers;
        }

        /// <summary>
        /// 根据表名填充约束信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillConstraints(Table table)
        {
            table.Constraints = ShowTableConstraints(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 返回指定表的所有约束
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<DBUtil.MetaData.Constraint> ShowTableConstraints(string tableName, string schemaName)
        {
            List<DBUtil.MetaData.Constraint> constraints = new List<DBUtil.MetaData.Constraint>();
            if (string.IsNullOrWhiteSpace(tableName)) return constraints;
            string sql = $@"
SELECT c.conname AS constraint_name,  
          CASE c.contype  
            WHEN 'c' THEN 'CHECK'  
            WHEN 'f' THEN 'FOREIGN KEY'  
            WHEN 'p' THEN 'PRIMARY KEY'  
            WHEN 'u' THEN 'UNIQUE'  
          END AS constraint_type,  
          CASE WHEN c.condeferrable = 'f' THEN 0 ELSE 1 END AS is_deferrable,  
          CASE WHEN c.condeferred = 'f' THEN 0 ELSE 1 END AS is_deferred,  
          t.relname AS table_name,  
          array_to_string(c.conkey, ' ') AS constraint_key,
          CASE confupdtype
            WHEN 'a' THEN 'NO ACTION'
            WHEN 'r' THEN 'RESTRICT'
            WHEN 'c' THEN 'CASCADE'
            WHEN 'n' THEN 'SET NULL'
            WHEN 'd' THEN 'SET DEFAULT'
          END AS on_update,  
          CASE confdeltype
            WHEN 'a' THEN 'NO ACTION'
            WHEN 'r' THEN 'RESTRICT'
            WHEN 'c' THEN 'CASCADE'
            WHEN 'n' THEN 'SET NULL'
            WHEN 'd' THEN 'SET DEFAULT'
          END AS on_delete,  
          CASE confmatchtype
            WHEN 'u' THEN 'UNSPECIFIED'
            WHEN 'f' THEN 'FULL'
            WHEN 'p' THEN 'PARTIAL'
          END AS match_type,  
          t2.relname AS references_table,  
          array_to_string(c.confkey, ' ') AS fk_constraint_key
     FROM pg_constraint c
LEFT JOIN pg_class t ON c.conrelid = t.oid
LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
    WHERE t.relname = '{tableName}'";
            var dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var constraint = new PostgreSqlConstraint();
                constraint.TableName = tableName;
                constraint.Name = dt.Rows[i]["constraint_name"].ToString();
                constraint.Constraintkeys = dt.Rows[i]["constraint_key"].ToString();
                constraint.Delete_Action = dt.Rows[i]["on_delete"].ToString();
                constraint.Update_Action = dt.Rows[i]["on_update"].ToString();
                constraints.Add(constraint);
                string type = (dt.Rows[i]["constraint_type"] ?? "").ToString();
                if (type.StartsWith("CHECK"))
                {
                    constraint.Type = EnumConstraintType.Check;
                }
                else if (type.StartsWith("DEFAULT"))
                {
                    constraint.Type = EnumConstraintType.DefaultValue;
                }
                else if (type.StartsWith("FOREIGN"))
                {
                    constraint.Type = EnumConstraintType.ForeignKey;
                }
                else if (type.StartsWith("PRIMARY KEY"))
                {
                    constraint.Type = EnumConstraintType.PrimaryKey;
                }
                else if (type.StartsWith("UNIQUE"))
                {
                    constraint.Type = EnumConstraintType.Unique;
                }
            }
            return constraints;
        }

        /// <summary>
        /// 根据表名称填充索引信息
        /// </summary>
        /// <param name="table"></param>
        private void FillIndexes(Table table)
        {
            table.Indexes = ShowTableIndexes(table.Name, table.SchemaName);
        }

        /// <summary>
        /// 返回指定表所有索引
        /// </summary>
        /// <param name="tableName">所属的表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<Index> ShowTableIndexes(string tableName, string schemaName)
        {
            List<Index> indexs = new List<Index>();
            string sql = $@"
SELECT t.relname, a.attname, a.attnum  
     FROM pg_index c  
LEFT JOIN pg_class t  
       ON c.indrelid  = t.oid  
LEFT JOIN pg_attribute a  
       ON a.attrelid = t.oid  
      AND a.attnum = ANY(indkey)  
    WHERE t.relname = 'testtbl'";
            var dt = db.SelectDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    Index index = new PostgreSqlIndex();
                    index.TableName = tableName;
                    index.ColumnNames = dt.Rows[i]["attname"].ToString();
                    index.Name = tableName + "--" + dt.Rows[i]["attname"].ToString();
                    indexs.Add(index);
                }
            }
            return indexs;
        }

        private Table FillTableInfo(string tableName)
        {
            throw new NotImplementedException();
        }

        public override List<Function> ShowFunctions()
        {
            throw new NotImplementedException();
        }

        public override List<Sequence> ShowSequences()
        {
            throw new NotImplementedException();
        }

        public override Result AddColumn(string tableName, Column column)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// truncate指定表
        /// </summary>
        /// <param name="tableName">模式名</param>
        /// <param name="shemaName">表名</param>
        public override void TruncateTable(string tableName, string shemaName)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 删除触发器
        /// </summary>
        /// <param name="triggerName">触发器名称</param>
        /// <returns></returns>
        public override Result DropTriggerIfExist(string triggerName)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 删除存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <returns></returns>
        public override Result DropProcedureIfExist(string procedureName)
        {
            throw new NotImplementedException();
        }

        public override (string tableName, string schemaName, string dbName) SplitTableName(string tableName)
        {
            throw new NotImplementedException();
        }

        public override View ShowViewDetail(string viewName, string schemaName)
        {
            throw new NotImplementedException();
        }

        public override List<Column> ShowTableColumns(string tableName, string schemaName)
        {
            throw new NotImplementedException();
        }

        public override Result CreateTable(Table table)
        {
            throw new NotImplementedException();
        }

        public override Result DropColumn(string tableName, string columnName)
        {
            throw new NotImplementedException();
        }

        public override Result DropTableIfExist(string tableName)
        {
            throw new NotImplementedException();
        }

        public override Result RenameTable(string oldName, string newName)
        {
            throw new NotImplementedException();
        }

        public override Result RenameColumn(string tableName, string oldName, string newName)
        {
            throw new NotImplementedException();
        }

        public override Result UpdateTableDescription(string tableName, string desc)
        {
            throw new NotImplementedException();
        }

        public override Result UpdateViewDescription(string tableName, string desc)
        {
            throw new NotImplementedException();
        }

        public override Result UpdateColumnDescription(string tableName, string colName, string desc)
        {
            throw new NotImplementedException();
        }

        public override (long count, string sql, long total) GenerateInsertSql(string schemaName, string tableName, int limitCount = 0, string filter = null, params IDataParameter[] paras)
        {
            throw new NotImplementedException();
        }

        public override long GenerateInsertSqlFile(string schemaName, string tableName, string fileAbsPath, int limitCount = 0, string filter = null, params IDataParameter[] paras)
        {
            throw new NotImplementedException();
        }

        public override long GetCount(string tableName, string schemaName)
        {
            throw new NotImplementedException();
        }

        public override string GenerateCreateTableSql(string tableName, string shemaName)
        {
            throw new NotImplementedException();
        }

        public override void GenerateCreateTableSqlFile(List<Table> tables, string fileAbsPath, bool includeInsertSql = false)
        {
            throw new NotImplementedException();
        }

        public override string GenerateCode(string tableName, string shemaName)
        {
            throw new NotImplementedException();
        }
    }
}
